Amazon Redshift: 現在のテーブル定義からCREATE TABLE文などをリバースエンジニアリングする
最近は(個人的な投稿に関しては)アドベントカレンダーでTableauネタが続いておりますが、ここいらでそれ以外のネタについてもぶっ込んで行こうかと思います。当エントリはAmazon Redshiftに関する小ネタです。
Amazon Redshiftではテーブル定義を割とフランクに変更する機会があるのではないか、と考えます。データ投入を行うテーブルを新しく追加する、そのテーブルの最適な定義(列の型や列圧縮タイプ、分散キー等)を見極めるための各種調査及び検証、そして仕様変更等...そんな感じでテーブルの定義を変えて行くと『あれ、今時点での定義ってどうなってるんだっけ？』となる事もしばしば発生します。修正の前に仕様書を書き換えて...と都度やるのも面倒っちゃぁ面倒。
という事で、現行の構成からCREATE TABLE文などの情報を取り出せるようなコードを、備忘録も兼ねてこちらのエントリに書き記しておこうと思います。
CREATE TABLE文を生成する(by Groovy)
まずはCREATE TABLE文の作成について。こちらSQLで頑張ってみようかと思ったのですが割と面倒そうだったので早々に心が折れました。手っ取り早く組むのにはスクリプト系言語かなと思い、Groovyで書いて見たのが以下コードとなります。お手持ちのローカル環境にGroovy稼働環境を準備頂くか、EC2上に実行環境を用意頂く等して頂ければと思います。
PostgreSQLのJDBCドライバの準備、また対象テーブルが所属するスキーマの検索パスへの追加(※参照)等が済んでいる状態で、以下コードに所定の設定内容を記載の後、実行する事でCREATE TABLE文が標準出力に表示されます。
分散スタイルに関する制御は、以下情報を参考にしています。対象となる数値が"1"であればKEY指定、"8"であればALL指定(それ以外は指定無し("0")という情報が取得出来るので、それを活用しています。
import groovy.sql.Sql;
sql = Sql.newInstance(
"<JDBC接続文字列>",
"<DB接続ユーザー名>",
"<DB接続パスワード>",
"org.postgresql.Driver");
String schemaName = "<スキーマ名>";
String tableName = "<テーブル名>";
StringBuilder createStatementItems = new StringBuilder();
Map<Integer, String> sortKeyMap = new HashMap<Integer, String>();
String distkeyItem = "";
String sqlStatement_ColumnInfo = "SELECT * FROM pg_table_def WHERE schemaname = '" + schemaName + "' AND tablename = '" + tableName + "';"
sql.eachRow(sqlStatement_ColumnInfo, {
String encoding = it.encoding;
int sortkey = it.sortkey;
/** Column Information */
String typeStr = "";
if (it.type.contains("character(")) {
typeStr = it.type.replaceFirst("character", "char").toUpperCase();
} else if (it.type.contains("character varying")) {
typeStr = it.type.replaceFirst("character varying", "VARCHAR");
} else if (it.type.contains("integer")) {
typeStr = "INT";
} else if (it.type.contains("numeric")) {
typeStr = it.type.replaceFirst("numecic","DECIMAL");
} else if (it.type.contains("timestamp")) {
typeStr = it.type.replaceFirst("timestamp without time zone","TIMESTAMP");
} else {
typeStr = it.type.toUpperCase();
}
if (it.encoding == "none") {
encoding = " encode raw";
} else {
encoding = " encode " + it.encoding;
}
String isNotNull = "";
if (it.notnull) { isNotNull = " NOT NULL"; }
createStatementItems.append(" " + it.column + " " + typeStr + encoding + isNotNull + ",\n");
/** distkey */
if (it.distkey) {
distkeyItem = it.column;
}
/** sortkey */
if (sortkey != 0) {
sortKeyMap.put(sortkey, it.column);
}
});
/** DISTKEY Settings. */
String sqlStatement_distStyle = "SELECT relname, reldiststyle FROM pg_class WHERE relname = '" + tableName + "';";
String diststyleStr = "";
sql.eachRow(sqlStatement_distStyle, {
String distmode = it.reldiststyle;
if(distmode == "1") {
// 1:KEY 分散キー指定あり
diststyleStr = "distkey(" + distkeyItem + ")"
} else if(distmode == "8") {
// 8:ALL diststyle ALL
diststyleStr = "diststyle ALL"
} else {
// 0:EVEN 指定無し
}
});
/** SORTKEY Settings. */
StringBuilder sortKeyStr = new StringBuilder();
if (sortKeyMap.size() > 0) {
sortKeyStr.append("sortkey(");
sortKeyMap.sort().each {
sortKeyStr.append(it.value + ", ");
}
sortkeyString = sortKeyStr.substring(0,sortKeyStr.toString().length()-2) + ")"
}
println "===================================="
println "== CREATE TABLE文 "
println "== schema : " + schemaName
println "== table : " + tableName
println "===================================="
println "CREATE TABLE " + schemaName + "." + tableName + " ("
println createStatementItems.toString().substring(0,createStatementItems.toString().length()-2);
println ")"
println diststyleStr;
println sortkeyString;
println ";";
テーブル項目のコメント文を生成する(by SQL)
こちらについては、以下RedshiftSQLまとめエントリでも紹介したSQL文のうちの1つを、SELECT項目の部分を置き換える形で実現。SELECT項目をばっさりカットし、単純に文字列連結させただけです。実行時にはスキーマ名及びテーブル名を指定してください。
SELECT
'COMMENT ON COLUMN ' || pg_table_def.tablename || '.' || pg_table_def.column || ' IS \'' || def_comment.column_comment || '\';'
FROM
pg_table_def,
(SELECT
pg_stat_user_tables.schemaname AS schema,
pg_stat_user_tables.relname AS tablename,
tablecom.description as table_comment,
pg_attribute.attname AS columnname,
colcom.description AS column_comment,
pg_attribute.attnum,
pg_attrdef.adsrc as default
FROM
pg_stat_user_tables,
pg_type,
pg_attribute
LEFT JOIN pg_description tablecom ON pg_attribute.attrelid = tablecom.objoid AND tablecom.objsubid = 0
LEFT JOIN pg_description colcom ON pg_attribute.attnum = colcom.objsubid AND pg_attribute.attrelid = colcom.objoid
LEFT JOIN pg_attrdef ON pg_attribute.attrelid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum
WHERE
pg_attribute.attrelid = pg_stat_user_tables.relid
AND pg_attribute.atttypid = pg_type.oid
AND pg_attribute.attnum > 0
AND pg_stat_user_tables.relname IN (
SELECT
pg_class.relname
FROM
pg_namespace JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
WHERE
pg_class.relkind = 'r'
AND pg_class.relname NOT LIKE 'pg_%'
AND pg_class.relname NOT LIKE 'sql_%' )
AND pg_stat_user_tables.schemaname = '<スキーマ名>'
AND pg_stat_user_tables.relname = '<テーブル名>'
ORDER BY
pg_stat_user_tables.schemaname,
pg_stat_user_tables.relname,
pg_attribute.attnum
) def_comment
WHERE
pg_table_def.tablename = def_comment.tablename
AND pg_table_def.column = def_comment.columnname
AND pg_table_def.schemaname = '<スキーマ名>'
AND pg_table_def.tablename = '<テーブル名>'
ORDER BY
def_comment.schema,
def_comment.tablename,
def_comment.attnum;
また、テーブル名のコメントについては割愛。適宜以下のフォーマットで設定を行うようにして頂ければと思います(手抜き
COMMENT ON TABLE <スキーマ名>.<テーブル名> IS '<テーブル名のコメント内容>';
まとめ
以上、テーブル定義情報収集のためのちょっとしたスクリプトのご紹介でした。2つ目のSQL等はひと通り情報が揃ってますので、同じ様な仕組みでEXCELに貼っ付けたりWikiに貼っ付けて利用するような文字列も生成出来るかと思います。プロジェクトや環境によってはテーブルの数が膨大な規模になってしまう事もあるかと思いますので、そう言った情報を管理して行く上でもこういったスクリプトも利用する機会が出てくる事かと思います。それら作業を行う際の一助になれば幸いです。こちらからは以上です。
![[レポート]AWSのデータ取り込み戦略 - Solving different data ingestion use cases with AWS (ANT330) #AWSreInvent](https://images.ctfassets.net/ct0aopd36mqt/3IQLlbdUkRvu7Q2LupRW2o/edff8982184ea7cc2d5efa2ddd2915f5/reinvent-2024-sessionreport-jp.jpg)
![[Redshift] テーブルへのアクセス権付与権限について調べてみた](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
